﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;//引入 SQLServer 数据库进行交互的类和方法

namespace bookssystem
{
    public partial class BookManagement : Form
    {
        public BookManagement()
        {
            InitializeComponent();
            Inquire();
        }
        SqlConnection Data = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\halcon\c#\数据库books\bookssystem\bookssystem\Data\WhiteBook(1).mdf;Integrated Security=True;Connect Timeout=30");//SqlConnection 类用于建立与 SQLServer 数据库文件的连接。
        private void Inquire()// 表格显示全部书方法
        {
            Data.Open();// 打开数据库
            string demand = "select * from Booktbl";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(demand, Data);// 获取数据
            SqlCommandBuilder builder = new SqlCommandBuilder(sqlDataAdapter);// 更新数据库
            var ds = new DataSet();// 小型数据库
            sqlDataAdapter.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
            Data.Close();// 关闭数据库
        }
        private void refer()// 表格显示全部书方法
        {
            Data.Open();// 打开数据库
            string demand = "select * from Booktbl where BCat='" + SelectiveCategory.SelectedItem.ToString() + "'";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(demand, Data);// 获取数据
            SqlCommandBuilder builder = new SqlCommandBuilder(sqlDataAdapter);// 更新数据库
            var ds = new DataSet();// 小型数据库
            sqlDataAdapter.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
            Data.Close();// 关闭数据库
        }
        private void Save_Click(object sender, EventArgs e)
        {
            if (BookName.Text == "" || BookWriter.Text == "" || BibliographicType.SelectedIndex == -1 || Count.Text == "" || Price.Text == "")
            {
                MessageBox.Show("信息未填写完整");
            }
            else
            {
                try
                {
                    Data.Open();// 打开数据库
                    string query = "insert into Booktbl values('" + BookName.Text + "','" + BookWriter.Text + "','" + BibliographicType.SelectedItem.ToString() + "', '" + Count.Text + "','" + Price.Text + "')";
                    SqlCommand cmd = new SqlCommand(query, Data);//SqlCommand 主要向数据库发送 增 删 改 查sql语句
                    cmd.ExecuteNonQuery();// 更新数据库数据返回更新的行数
                    MessageBox.Show("信息保存完成");
                    Data.Close();// 关闭数据库
                    Inquire();
                    Passwords();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
        private void SelectiveCategory_SelectionChangeCommitted(object sender, EventArgs e)
        {
            refer();
        }

        private void Update_Click(object sender, EventArgs e)
        {
            Inquire();
            SelectiveCategory.SelectedIndex = -1;
        }
        public void Passwords()
        {
            BookName.Text = ""; 
            BookWriter.Text = ""; 
            BibliographicType.SelectedIndex = -1; 
            Count.Text = ""; 
            Price.Text = "";
        }

        private void Reset_Click(object sender, EventArgs e)
        {
            Passwords();
        }
        int index = 0;
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)// 表格显示数据
        {
            BookName.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();// 控件中当前选中行的第一行（索引为 0 ）中第二列（索引为 1 ）单元格的值。
            BookWriter.Text= dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
            BibliographicType.SelectedItem= dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
            Count.Text = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
            Price.Text = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();
           // MessageBox.Show(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
            if (BookName.Text=="")
            {
                index = 0;
            }
            else
            {
                index = int.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
            }
        }

        private void Delete_Click(object sender, EventArgs e)
        {
            if (index == 0)
            {
                MessageBox.Show("未选中你要删除的数据");
            }
            else {
                try
                {
                    Data.Open();// 打开数据库
                    string query = " delete from Booktbl where BId='" + index + "'";
                    SqlCommand cmd = new SqlCommand(query, Data);//SqlCommand 主要向数据库发送 增 删 改 查sql语句
                    cmd.ExecuteNonQuery();// 更新数据库数据返回更新的行数
                    Data.Close();// 关闭数据库
                    MessageBox.Show("数据已删除");
                    Inquire();
                    Passwords();
                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }
            
            
            }
        }

        private void Compile_Click(object sender, EventArgs e)
        {
            if (BookName.Text == "" || BookWriter.Text == "" || BibliographicType.SelectedIndex == -1 || Count.Text == "" || Price.Text == "")
            {
                MessageBox.Show("信息未填写完整");
            }
            else {
                try
                {
                    Data.Open();// 打开数据库
                    /*string query = " updata Booktbl set BTitle='" + BookName.Text + "', BAuthor='" + BookWriter.Text + "',BCat='" + BibliographicType.SelectedItem.ToString() + "',BQty='" + Count.Text + "',BPrice='" + Price.Text + "',where BId='"+index+"'";*/
                    string query = "UPDATE Booktbl SET BTitle='" + BookName.Text + "', BAuthor='" + BookWriter.Text + "', BCat='" + BibliographicType.SelectedItem.ToString() + "', BQty='" + Count.Text + "', BPrice='" + Price.Text + "' WHERE BId='" + index + "'";
                    SqlCommand cmd = new SqlCommand(query, Data);//SqlCommand 主要向数据库发送 增 删 改 查sql语句
                    cmd.ExecuteNonQuery();// 更新数据库数据返回更新的行数
                    Data.Close();// 关闭数据库
                    MessageBox.Show("信息已编辑成功");
                    Inquire();
                    Passwords();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            
            }
        }
    }
}
